﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using InfoSource.Models.objects;
using System.Text.RegularExpressions;

namespace InfoSource.Data
{
    public class AppointmentRemindersRequests
    {
        public static List<EmailTemplate> GetAppointmentReminderEmailTemplates(){
            InfoSourceDB db = new InfoSourceDB();
            var templates = db.t_EmailTemplates.Where(t => t.TemplateUsage == "appointment reminder");

            List<EmailTemplate> list = new List<EmailTemplate>();

            foreach (t_EmailTemplates template in templates)
            {
                EmailTemplate emailTemplate = new EmailTemplate();
                emailTemplate.ID = template.TemplateID;
                emailTemplate.TemplateName = template.TemplateName;
                emailTemplate.TemplateUsage = template.TemplateUsage;
                emailTemplate.TemplateBody = HttpUtility.HtmlDecode(template.TemplateBody);

                list.Add(emailTemplate);
            }

            return list;
        }

        /*--------------------------------------------------------------------*/
        public static DBResult SaveTemplate(int? id, string templateName, string templateBody)
        {
            DBResult result = new DBResult();
            InfoSourceDB db = new InfoSourceDB();

            t_EmailTemplates template = null;

            try
            {
                if (id.HasValue)
                {
                    template = db.t_EmailTemplates.Where(t => t.TemplateID == id).FirstOrDefault();
                }

                if (template == null)
                {
                    template = new t_EmailTemplates();
                    template.TemplateName = templateName;
                    template.TemplateBody = HttpUtility.HtmlEncode(templateBody);
                    template.TemplateUsage = "appointment reminder";

                    db.AddTot_EmailTemplates(template);
                }
                else
                {
                    template.TemplateName = templateName;
                    template.TemplateBody = HttpUtility.HtmlEncode(templateBody);
                }

                result.StatusCode = db.SaveChanges();
                result.StatusMsg = "success";
                result.AdditionalData.Add("TemplateID", template.TemplateID);
            }
            catch (Exception e)
            {
                result.StatusCode = -1;
                result.StatusMsg = e.Message;
            }


            return result;

        }

        /*--------------------------------------------------------------------*/
        public static DBResult DeleteTemplate(int templateID)
        {
            DBResult result = new DBResult();
            InfoSourceDB db = new InfoSourceDB();

            try
            {
                t_EmailTemplates template = new t_EmailTemplates();
                template.TemplateID = templateID;
                db.t_EmailTemplates.Attach(template);
                db.t_EmailTemplates.DeleteObject(template);

                result.StatusCode = db.SaveChanges();
                result.StatusMsg = "success";
            }
            catch (Exception e)
            {
                result.StatusCode = -1;
                result.StatusMsg = e.Message;
            }

            return result;
        }

		/*--------------------------------------------------------------------*/
		public static DBResult SearchAccounts(string searchType, string searchString)
		{
			DBResult result = new DBResult();
			var contracts = new List<Contract>();
			AdvServDB db = new AdvServDB();
			IQueryable<Contract> dbContracts = db.Contracts.AsQueryable<Contract>();
			

			switch (searchType)
			{
				case "accountnum":
					
					decimal acct = 0;
					decimal.TryParse(searchString, out acct);
					var dbCustomers = db.Customers.Where(cust => cust.id_ba == acct);

					dbContracts = from contract in dbContracts
								join customer in dbCustomers on contract.Customer_ID equals customer.Customer_ID
								select contract;

					contracts = dbContracts.ToList();

					break;


				case "address":

					string[] addressParts = searchString.Split(' ');
					if (addressParts.Length == 0) break;

					string houseNum = "";
					string streetAddr = "";
					
					if (Regex.IsMatch(addressParts[0], "^[0-9]+$"))
					{
						houseNum = addressParts[0];
						streetAddr = string.Join(" ", addressParts.Skip(1).ToArray());	
					}
					else
					{
						streetAddr = string.Join(" ", addressParts);
					}

					if (houseNum != "")
					{
						dbContracts = dbContracts.Where(c => c.Serv_House_No == houseNum);
					}

					dbContracts = dbContracts.Where(c => c.Serv_addr.Contains(streetAddr));


					contracts = dbContracts.ToList();

					break;

				case "name":

					string[] nameParts = searchString.Split(' ');
					if (nameParts.Length == 0) break;

					string firstName = "";
					string lastName = "";

					if (nameParts.Length > 0)
					{
						firstName = nameParts[0].ToLower();
 						lastName = string.Join(" ", nameParts.Skip(1).ToArray()).ToLower();

						dbContracts = dbContracts.Where(c => (c.First_Name.ToLower().Contains(firstName)
																	&& c.Last_Name.ToLower().Contains(lastName)
																)
																|| c.First_Name.ToLower().Contains(searchString)
																|| c.Last_Name.ToLower().Contains(searchString)
														);

						contracts = dbContracts.ToList();

					}



					break;
			}


			var searchResults = contracts.Take(20).Select(c => new
				{
					c.Customer_ID,
					c.Contract_ID,
					First_Name = c.First_Name.Trim(),
					Last_Name = c.Last_Name.Trim(),
					Serv_House_No = c.Serv_House_No.Trim(),
					Serv_addr = c.Serv_addr.Trim(),
					Serv_City = c.Serv_City.Trim()
				}
			);

			result.StatusCode = 1;
			result.AdditionalData.Add("results", searchResults);

			return result;
		}

		/*--------------------------------------------------------------------*/
		public static DBResult SearchAccounts(int contractID)
		{
			DBResult result = new DBResult();
			AdvServDB db = new AdvServDB();

			var contracts = db.Contract_appl.Where(ca => ca.Contract_ID == contractID)
											.Select(ca =>
												new
												{
													ca.Contract_ID,
													ca.ContApp_ID,
													ca.Appliance_ID,
													Appliance = db.Appliances.FirstOrDefault(a => a.Appliance_ID == ca.Appliance_ID).Description.Trim()
												}
											);

			result.StatusCode = 1;
			result.AdditionalData.Add("results", contracts);

			return result;
		}

		/*--------------------------------------------------------------------*/
		public static Contract GetSingleContract(int contractID)
		{
			AdvServDB db = new AdvServDB();

			Contract contract = db.Contracts.Where(ca => ca.Contract_ID == contractID).FirstOrDefault();

			return contract;
		}

		/*--------------------------------------------------------------------*/
		public static Appliance GetSingleAppliance(int applianceID)
		{
			AdvServDB db = new AdvServDB();

			Appliance appliance = db.Appliances.Where(appl => appl.Appliance_ID == applianceID).FirstOrDefault();

			return appliance;
		}

		/*--------------------------------------------------------------------*/
		public static t_EmailTemplates GetSingleTemplate(int templateID)
		{
			InfoSourceDB db = new InfoSourceDB();

			t_EmailTemplates template = db.t_EmailTemplates.Where(tpl => tpl.TemplateID == templateID).FirstOrDefault();

			return template;
		}
	}
}